#!/bin/bash

# 定义变量方便修改
table_db=cy_database.
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
with
tmp1_table as 最近7天新增
(
select
	478472 num,
	null percent1,
	'人才总量' content,
	'人才总量' name,
	cast(current_date() as string) as operate_time

union all
select
	1421 num,
	null percent1,
	'近七天新增' content,
	'人才总量' name,
	cast(current_date() as string) as operate_time
),



tmp2_table as 没有长垣市的数据暂用广州的代替
(
select
	count(1) num,
	null percent1,
	'本地就业' content,
	'人才总量' name,
	cast(current_date() as string) as operate_time
from cy_database.dws_person_detail
where census_register = '440000'

union all
select
	count(1) num,
	null percent1,
	'本地就业' content,
	'人才总量' name,
	cast(current_date() as string) as operate_time
from cy_database.dws_person_detail
where census_register != '440000'

),




tmp3_table1 as
(
select
	sum(num1) sum1,
	sum(num2) sum2,
	count(1) amount
from (
	select
		case when startup_code is null then 1 end as num1,
		case when startup_code is not null then 1 end as num2
	from cy_database.dws_person_detail
	)a
),

tmp3_table2 as
(
select
	sum1 as num,
	cast((sum1/amount) as decimal(10,4)) as percent1,
	'失业人数' content,
	'人才总量' name,
	cast(current_date() as string) as operate_time
from tmp3_table1
union all
select
	sum1 as num,
	cast((sum2/amount) as decimal(10,4)) as percent1,
	'就业人数' content,
	'人才总量' name,
	cast(current_date() as string) as operate_time
from tmp3_table1
),



tmp4_table as 个人详情表没有这些标签
(
select
	478 num,
	null percent1,
	'高科技人才' content,
	'人才总量' name,
	cast(current_date() as string) as operate_time
union all
select
	996 num,
	null percent1,
	'城镇失业人口' content,
	'人才总量' name,
	cast(current_date() as string) as operate_time

union all
select
	15810 num,
	null percent1,
	'高校毕业生' content,
	'人才总量' name,
	cast(current_date() as string) as operate_time

union all
select
	239 num,
	null percent1,
	'退伍军人' content,
	'人才总量' name,
	cast(current_date() as string) as operate_time

union all
select
	911 num,
	null percent1,
	'就业困难人士' content,
	'人才总量' name,
	cast(current_date() as string) as operate_time
)




insert overwrite table cy_database.ads_big_screen_result_rc_amount
select num,percent1,content,name,operate_time from tmp1_table

union all
select num,percent1,content,name,operate_time from tmp2_table

union all
select num,percent1,content,name,operate_time from tmp3_table2

union all
select num,percent1,content,name,operate_time from tmp4_table

;
"
$hive -e "$sql"